# -*- coding: utf-8 -*-

#    OriginDB
#    Copyright (C) 2009  Jason Power <power.jg@gmail.com>
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 2 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License along
#    with this program; if not, write to the Free Software Foundation, Inc.,
#    51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

import MySQLdb


class _ChemDB:

	def __init__(self, errorHandler=None):

		self.errorHandler = errorHandler
		self.cursor = None

		self.uid = -1

	def userLoggedIn(self):
		return self.uid != -1

	def reconnect(self, user, passwd, db='', host=''):
		print "reconnecting as user", user

		if not db:
			try:
				db = self.dbInfo['db']
			except AttributeError:
				db = "chemdb"
		if not host:
			try:
				host = self.dbInfo['host']
			except AttributeError:
				host = "localhost"

		self.dbInfo = {'db':db, 'host':host, 'user':user, 'passwd':passwd}
		
		if self.cursor:
			del self.cursor
		self.db = MySQLdb.connect(db=self.dbInfo['db'], user=self.dbInfo['user'], host=self.dbInfo['host'], passwd=self.dbInfo['passwd'])
		self.cursor = self.db.cursor()
		
		if self.errorHandler:
			self.setErrorHandler(self.errorHandler)

	def setUid(self, uid):
		self.uid = uid

	def setErrorHandler(self, errorHandler):
		self.errorHandler = errorHandler
		try:
			self.cursor.errorhandler = errorHandler
		except AttributeError:
			pass

	def insertUser(self, name, passwd, email, chemdb_pass):
		passwd = passwd.replace("\\","\\\\")
		passwd = passwd.replace("'","\\\'")
		passwd = passwd.replace('"','\\\"')
		passwd = passwd.replace("\0","\\0")
		chemdb_pass = chemdb_pass.replace("\\","\\\\")
		chemdb_pass = chemdb_pass.replace("'","\\'")
		chemdb_pass = chemdb_pass.replace('"',"\"")
		chemdb_pass = chemdb_pass.replace("\0","\\0'")

		query = """INSERT INTO user (username, password, email, chem_pass)
				VALUES ('%s', "%s", '%s', "%s"); """ % (name, passwd, email, chemdb_pass)

		self.cursor.execute(query)
		id = self.cursor.lastrowid
		self.db.commit()

		return id

	def updatePassword(self, uid, passwd, chemdb_pass):
		passwd = passwd.replace("\\","\\\\")
		passwd = passwd.replace("'","\\\'")
		passwd = passwd.replace('"','\\\"')
		passwd = passwd.replace("\0","\\0")
		chemdb_pass = chemdb_pass.replace("\\","\\\\")
		chemdb_pass = chemdb_pass.replace("'","\\'")
		chemdb_pass = chemdb_pass.replace('"',"\"")
		chemdb_pass = chemdb_pass.replace("\0","\\0'")

		query = """UPDATE user SET password='%s', chem_pass='%s' WHERE id='%i';""" % (passwd, chemdb_pass, uid)

		self.cursor.execute(query)
		self.db.commit()

	#def insertOBMolecule(self, molecule):

		#name = molecule.title()

		#inchi = molecule.write('inchi').strip('/n')

		#mol = molecule.write('mol')

		#fp = molecule.calcfp()

		##for fingerprint use fp.findbits()
		##it tells you where the set bits are
		##store that and when we want to run
		##a test on it test it ourselves with
		##something like "is this bit set here?"
		##the tanimoto coef is defined by:
		##Number of bits set in (patternFP & targetFP) /
		##Number of bits in (patternFP | targetFP)
		##this is going to be very very slow
		##maybe think of a better way, maybe not.S

		##to convert back to bits run
		##bits = [int(i) for i in fpstr.split(',')[0:-1]]
		##to hash do
		##hash = dict([(int(i),None) for i in fpstr.split(',')[0:-1]])
		#fpstr = ''
		#for i in fp.bits:
			#fpstr += str(i)+','


		#formula = molecule.formula

		##inserting name and formula
		#query = """ INSERT INTO compound (name,formula)
			#VALUES ('%s','%s');""" % (name, formula)
		#self.cursor.execute(query)

		##get the compound id
		#query = """SELECT id FROM compound WHERE name=%s;""" % (name)
		#self.cursor.execute(query)

		#id = self.cursor.fetchone()[0]

		##inserting inchi
		#query = """ INSERT INTO compound_inchi (compound_id,inchi)
				#VALUES ('%s','%s');""" % (id, inchi)
		#self.cursor.execute(query)

		##inserting mol
		#query = """ INSERT INTO compound_mol (compound_id,molfile)
				#VALUES ('%s','%s');""" % (id, mol)
		#self.cursor.execute(query)

		##inserting fingerprint
		#query = """ INSERT INTO compound_fp (compound_id,fingerprint)
				#VALUES ('%s','%s');""" % (id, fpstr)
		#self.cursor.execute(query)

		##commits the changes
		#self.db.commit()


	def insertCompound(self, name='', formula='', IUPACname=''):
		"""Insert a compound into the database. IUPACname is optional.
		Returns compoundId."""

		if IUPACname and name and formula:
			query = """INSERT INTO compound (name, formula, IUPACname, uid)
			VALUES ('%s', '%s', '%s', '%i')""" % (name, formula, IUPACname, self.uid)
		elif name and formula:
				query = """INSERT INTO compound (name, formula, uid)
				VALUES ('%s', '%s', '%i')""" % (name, formula, self.uid)
		elif name:
			query = """INSERT INTO compound (name, uid)
			VALUES ('%s', '%i')""" % (name, self.uid)
		else:
			query = """INSERT INTO compound (uid)
			VALUES ('%i')""" % (self.uid)

		self.cursor.execute(query)
		id = self.cursor.lastrowid
		self.db.commit()

		return id


	def insertInchi(self, compoundId, inchi=""):
		"""Insert Inchi formula into database for compoundId.
		Returns inchi id."""

		if compoundId == -1:
			return -1

		if inchi:
			query = """INSERT INTO compound_inchi (inchi, compound_id, uid)
			VALUES ('%s', '%i', '%i')""" % (inchi, compoundId, self.uid)
		else:
			query = """INSERT INTO compound_inchi (compound_id, uid)
			VALUES ('%i', '%i');""" % (compoundId, self.uid)

		self.cursor.execute(query)
		id = self.cursor.lastrowid
		self.db.commit()

		return id


	def insertMol(self, compoundId, molfile=""):
		"""Insert molfile into database for compoundId.
		Returns molfile id."""

		if compoundId == -1:
			return -1

		if molfile:
			query = """INSERT INTO compound_mol (molfile, compound_id, uid)
			VALUES ('%s', '%i', '%i')""" % (molfile, compoundId, self.uid)
		else:
			query = """INSERT INTO compound_mol (compound_id, uid)
			VALUES ('%i', '%i')""" % (compoundId, self.uid)

		self.cursor.execute(query)
		id = self.cursor.lastrowid
		self.db.commit()

		return id


	def insertFingerprint(self, compoundId=-1, fp=""):
		"""Insert molfile into database for compoundId.
		Returns molfile id."""

		query = "INSERT INTO compound_fp ("
		end = "VALUES ("

		if fp:
			query += "fingerprint, "
			end += "'%s', " % fp
		if compoundId:
			query += "compound_id, "
			end += "'%s', " % compoundId

		query += "uid) "
		end += "'%i');" % self.uid

		query += end

		self.cursor.execute(query)
		id = self.cursor.lastrowid
		self.db.commit()

		return id


	def insertRelation(self, reactantId, productId, reactionId=None):
		"""Insert relation such that reactId is the compoundId
		of the reactant and prodId is the product.
		Returns relation id.
		"""

		if reactionId:
			query = """ INSERT INTO relation (reactant_id, product_id, reaction_id, uid)
					VALUES ('%s','%s','%s', '%i');""" % (reactantId, productId, reactionId, self.uid)
		else:
			query = """ INSERT INTO relation (reactant_id, product_id, uid)
					VALUES ('%s','%s', '%i');""" % (reactantId, productId, self.uid)

		self.cursor.execute(query)
		id = self.cursor.lastrowid
		self.db.commit()

		return id


	def insertReference(self, name='', authorName='', year='', source=''):
		"""Insert reference. The author name, year, and source
		are optional. Returns reference id.
		"""
		#Take out author_name, year, source if not given...
		query = """ INSERT INTO reference ("""
		end = "VALUES ("

		if name:
			query += "name, "
			end += "'%s', " % name
		if authorName:
			query += "author_name, "
			end += "'%s', " % authorName
		if year:
			query += "year, "
			end += "'%s', " % year
		if source:
			query += "source, "
			end += "'%s', " % source

		query += "uid) "
		end += "'%i');" % self.uid

		query += end

		self.cursor.execute(query)
		id = self.cursor.lastrowid
		self.db.commit()

		return id


	def insertReaction(self, referenceId=-1, temperature=-1000, solvent='', state='',pressure=0.0 ,time=-1, pH=0.0, other=''):
		"""Insert a reaction. Everything is optional. Other things refer to this
		table. Returns reaction id."""

		#Take out everything if noty given
		query = """ INSERT INTO reaction ("""
		end = "VALUES ("

		if referenceId != -1:
			query += "reference_id, "
			end += "'%i', " % referenceId
		if temperature != -1000:
			query += "temperature, "
			end += "'%i', " % temperature
		if solvent:
			query += "solvent, "
			end += "'%s', " % solvent
		if state:
			query += "state, "
			end += "'%s', " % state
		if pressure != 0:
			query += "pressure, "
			end += "'%f', " % pressure
		if time != -1:
			query += "time, "
			end += "'%f', " % time
		if pH != 0:
			query += "pH, "
			end += "'%f', " % pH
		if other:
			query += "other, "
			end += "'%s', " % other

		query += "uid) "
		end += "'%i');" % self.uid

		query += end

		print query

		self.cursor.execute(query)
		id = self.cursor.lastrowid
		self.db.commit()

		return id


	def insertCatalyst(self, name="", reactionId=None):
		"""Inserts a catalyst. Must have reactionId and name.
		This is actually part of the reaction idea, but there
		could be any number of catalysts so it has its own table.
		Returns catalyst id."""

		if name:
			query = """ INSERT INTO catalyst (name, uid)
			VALUES ('%s', '%s', '%i')""" % (name, self.uid)
		else:
			query = """ INSERT INTO catalyst (uid)
			VALUES ('%i')""" % (self.uid)

		self.cursor.execute(query)
		id1 = self.cursor.lastrowid
		
		if reactionId and id1 != -1:
			query = """ INSERT INTO react_cat (reaction_id, catalyst_id)
			VALUES ('%i', '%i')""" % (id1, reactionId)
		
		self.cursor.execute(query)
		id2 = self.cursor.lastrowid
		
		self.db.commit()
		
		if id1==-1 or id2==-1:
			return -1
		else:
			return id1

	def getUserInfo(self, username):

		query = """SELECT id, password FROM user WHERE username = '%s'""" % (username)

		num = self.cursor.execute(query)
		if num==1:
			info = self.cursor.fetchone()
			return int(info[0]), info[1]
		else:
			return -1, ""

	def get(self, column, table, key, value):

		query = """SELECT %s FROM %s WHERE %s = '%s';""" % (column, table, key, value)
		num = self.cursor.execute(query)
		if num==1:
			return self.cursor.fetchone()[0]
		elif num==0:
			return None
		else:
			raise DatabaseError(query, "Expecting different number of rows")

	def getMany(self, column, table, key, value, maxItems=100):
		""" Returns list of relationIds such that all of them are
			products for the given reactant ID
		"""

		query = """SELECT %s FROM %s WHERE %s = '%s';""" % (column, table, key, value)

		num = self.cursor.execute(query)

		if num == 0:
			return []
		elif num < maxItems:
			ans = [i[0] for i in self.cursor.fetchall()]
			return ans
		else:
			raise DatabaseError(query, "Too many results")
			
	
	def updateCompound(self, id, key, value):

		if not value:
			return

		query = """ UPDATE compound SET %s = '%s' WHERE id=%i""" % (key, value, id)

		self.cursor.execute(query)
		self.db.commit()

	def updateInchi(self, id, key, value):

		if not value:
			return

		query = """ UPDATE compound_inchi SET %s = '%s' WHERE id=%i""" % (key, value, id)

		self.cursor.execute(query)
		self.db.commit()

	def updateMolfile(self, id, key, value):

		if not value:
			return

		query = """ UPDATE compound_mol SET %s = '%s' WHERE id=%i""" % (key, value, id)

		self.cursor.execute(query)
		self.db.commit()

	def updateFingerprint(self, id, key, value):

		if not value:
			return

		query = """ UPDATE compound_fp SET %s = '%s' WHERE id=%i""" % (key, value, id)

		self.cursor.execute(query)
		self.db.commit()

	def updateRelation(self, id, key, value):

		if not value:
			return

		query = """ UPDATE relation SET %s = '%s' WHERE id=%i""" % (key, value, id)

		self.cursor.execute(query)
		self.db.commit()

	def updateReference(self, id, key, value):

		if not value:
			return

		query = """ UPDATE reference SET %s = '%s' WHERE id=%i""" % (key, value, id)

		self.cursor.execute(query)
		self.db.commit()

	def updateReaction(self, id, key, value):

		if not value:
			return

		query = """ UPDATE reaction SET %s = '%s' WHERE id=%i""" % (key, value, id)

		self.cursor.execute(query)
		self.db.commit()

	def updateCatalyst(self, id, key, value):

		if not value:
			return

		query = """ UPDATE catalyst SET %s = '%s' WHERE id=%i""" % (key, value, id)

		self.cursor.execute(query)
		self.db.commit()

	def searchLike(self, data, table):
		if len(data) == 0:
			return []

		query = """ SELECT id FROM %s """ % (table)

		for key in data:
			query += """WHERE %s LIKE "%%%s%%" OR """ % (key, data[key])
		query = query[:-4] + ';'
		print query

		self.cursor.execute(query)

		ids = self.cursor.fetchall()

		return ids

	def searchByInchi(self, inchi):

		query = """ SELECT compound_id FROM compound_inchi WHERE inchi='%s' """ % (inchi)

		self.cursor.execute(query)

		ids = self.cursor.fetchall()

		return ids

	def searchReactants(self, compoundId):

		query = """ SELECT reactant_id, id FROM relation WHERE product_id='%i'""" % (compoundId)

		self.cursor.execute(query)

		info = self.cursor.fetchall()

		return info

	def searchProducts(self, compoundId):

		query = """ SELECT product_id, id FROM relation WHERE reactant_id='%i'""" % (compoundId)

		self.cursor.execute(query)

		info = self.cursor.fetchall()

		return info
		
	
	def deleteRelation(self, relationId):
		
		query = """ DELETE FROM relation WHERE id='%i'""" % (relationId)

		self.cursor.execute(query)
		
		self.db.commit()
		
	def removeCatalyst(self, catalystId, reactionId):
		
		query = """ DELETE FROM react_cat WHERE 
		(reaction_id='%i' and catalyst_id='%i'""" % (reactionId, catalystId)

		self.cursor.execute(query)
		
		self.db.commit()


class DatabaseError(Exception):

	def __init__(self, query, message):
		self.query = query
		self.message = message

	def __str__(self):
		return 'Query failed: '+self.query, "with message: "+self.message

#placeholder for singleton pattern
dbHandle = _ChemDB()
